package com.binguo.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.binguo.common.exception.ServiceException;
import com.binguo.model.api.vo.GeneralSqlVO;
import com.binguo.model.mapper.CommonQueryMapper;
import com.binguo.model.mapper.ISqlMapper;
import com.binguo.model.model.CommonQuery;
import com.binguo.model.wxapi.vo.ISqlResultVO;
import com.binguo.model.wxapi.vo.ISqlVO;
import com.binguo.model.wxapi.vo.ReplaceSqlResultVO;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import tk.mybatis.mapper.entity.Condition;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author yuritian
 * @Title: ISqlService
 * @ProjectName binguoDevelop
 * @Description: TODO
 * @date 2020/4/1113:23
 */
@Service
public class ISqlService {
    private static Logger logger = LoggerFactory.getLogger(ISqlService.class);

    private final String selectSqlAdd = "select a.* from(";

    private final String countSqlAdd = "select count(*) from(";

    @Resource
    private SqlSessionFactory sqlSessionFactory;

    @Resource
    private SqlSession sqlSession;

    @Resource
    private CommonQueryMapper commonQueryMapper;

    public List selectSql(String data) {
        ISqlMapper sqlMapper = sqlSession.getMapper(ISqlMapper.class);
        logger.info(data);
        List list = sqlMapper.selectList(data);
        logger.info(JSON.toJSONString(list));
        return list;
    }

    public String countSql(String data){
        ISqlMapper sqlMapper = sqlSession.getMapper(ISqlMapper.class);
        logger.info(data);
        String result = sqlMapper.selectOne(data);
        logger.info(result);
        return result;
    }

    public ISqlResultVO runSql(ISqlVO iSqlVO) {
        ISqlResultVO resultVO = new ISqlResultVO();
        Integer pageSize = iSqlVO.getPage_size();
        resultVO.setPage_num(iSqlVO.getPage_num());
        resultVO.setPage_size(pageSize);
        Condition condition = new Condition(CommonQuery.class);
        condition.or().andEqualTo("sqlId",iSqlVO.getSql_id());
        List<CommonQuery> commonQueryList = commonQueryMapper.selectByCondition(condition);
        if (null == commonQueryList || commonQueryList.size() == 0){
            throw new ServiceException("sqlId不存在");
        }
        CommonQuery commonQuery = commonQueryList.get(0);
        resultVO.setSql_id(commonQuery.getSqlId());
        resultVO.setSql_name(commonQuery.getSqlName());

        String sqlJsonInit = commonQuery.getParamContent();

        String sql = commonQuery.getSqlContent();

        JSONArray sqlArray = JSONArray.parseArray(sqlJsonInit);

        JSONObject webJson = iSqlVO.getParams();

        List<String> musthaveList = getList(sql);

        Integer index = 0;

        for (int i = 0; i < sqlArray.size(); i++) {
            String replaceValue;
            GeneralSqlVO generalSqlVO = JSON.parseObject(sqlArray.get(i).toString(), GeneralSqlVO.class);
            if (!webJson.containsKey(generalSqlVO.getName())) {
                if (generalSqlVO.getMusthave().toUpperCase().equals("FALSE")) {
                    StringBuilder sqlBuilder = new StringBuilder(sql);
                    String musthaveStr = musthaveList.get(index);
                    if (musthaveStr.length() - musthaveStr.replace("?","").length() > 1){
                        i += musthaveStr.length() - musthaveStr.replace("?","").length() - 1;
                    }
                    Integer sqlIndex = sql.indexOf(musthaveList.get(index));
                    sql = sqlBuilder.replace(sqlIndex, sqlIndex + musthaveList.get(index).length(), "").toString();
                    index++;
                    continue;
                } else {
                    throw new ServiceException(generalSqlVO.getName() + "为必填项,请检查Json里是否有这该值,数值类型是否正确");
                }
            }
            //like查询拼接
            if (generalSqlVO.getQuery_like() != null) {
                replaceValue = webJson.getString(generalSqlVO.getName());
                if (generalSqlVO.getQuery_like().equals("AROUND")) {
                    replaceValue = "'%" + replaceValue + "%'";
                } else if (generalSqlVO.getQuery_like().equals("LEFT")) {
                    replaceValue = "'%" + replaceValue + "'";
                } else if (generalSqlVO.getQuery_like().equals("RIGHT")) {
                    replaceValue = "'" + replaceValue + "%";
                }
                ReplaceSqlResultVO replaceSqlResultVO = replaceSql(sql, replaceValue, musthaveList, generalSqlVO, index);
                sql = replaceSqlResultVO.getSql();
                index = replaceSqlResultVO.getIndex();
                musthaveList = replaceSqlResultVO.getMusthaveList();
                continue;
            }

            //in查询拼接
            if (generalSqlVO.getQuery_in() != null) {
                replaceValue = webJson.getString(generalSqlVO.getName());
                if (generalSqlVO.getType().equals("STRING")) {
                    String[] replaceValueSqilt = replaceValue.split(",");
                    replaceValue = "(";
                    for (int j = 0; j < replaceValueSqilt.length - 1; j++) {
                        replaceValue += "'" + replaceValueSqilt[j] + "',";
                    }
                    replaceValue += "'" + replaceValueSqilt[replaceValueSqilt.length - 1] + "')";
                } else {
                    replaceValue = "(" + replaceValue + ")";
                }
                ReplaceSqlResultVO replaceSqlResultVO = replaceSql(sql, replaceValue, musthaveList, generalSqlVO, index);
                sql = replaceSqlResultVO.getSql();
                index = replaceSqlResultVO.getIndex();
                musthaveList = replaceSqlResultVO.getMusthaveList();
                continue;
            }

            //普通拼接
            if (generalSqlVO.getType().equals("STRING")) {
                replaceValue = webJson.getString(generalSqlVO.getName());
                replaceValue = "'" + replaceValue + "'";
            } else {
                replaceValue = webJson.getInteger(generalSqlVO.getName()).toString();
            }
            ReplaceSqlResultVO replaceSqlResultVO = replaceSql(sql, replaceValue, musthaveList, generalSqlVO, index);
            sql = replaceSqlResultVO.getSql();
            index = replaceSqlResultVO.getIndex();
            musthaveList = replaceSqlResultVO.getMusthaveList();
        }
        sql = sql.replaceAll("\r|\n", " ");
        String countSql = countSqlAdd + sql + ") as a";
        Integer count = Integer.parseInt(countSql(countSql));
        if (iSqlVO.getPage_num() != null){
            resultVO.setRecord_count(count);
            if (count%pageSize == 0){
                resultVO.setAll_page_num(count/pageSize);
            }else {
                resultVO.setAll_page_num(count/pageSize + 1);
            }
            if (iSqlVO.getPage_num() > resultVO.getAll_page_num()){
                resultVO.setPage_num(resultVO.getAll_page_num());
            }
            sql = selectSqlAdd + sql + ") as a";
            sql += " limit " + resultVO.getPage_size() * (resultVO.getPage_num() - 1) + " , " + resultVO.getPage_size();
        }

        if (!count.equals(0)) {
            resultVO.setResults(selectSql(sql));
        }
        return resultVO;
    }

    private ReplaceSqlResultVO replaceSql(String sql,
                                          String replaceValue,
                                          List<String> musthaveList,
                                          GeneralSqlVO generalSqlVO,
                                          Integer index) {
        ReplaceSqlResultVO resultVO = new ReplaceSqlResultVO();
        if (generalSqlVO.getMusthave().equals("FALSE")) {
            String replaceStr = musthaveList.get(index);
            replaceStr = replaceStr.replaceFirst("[?]", replaceValue);
            if (replaceStr.contains("?")){
                //当前大括号存在问号
                sql = sql.replace(musthaveList.get(index), replaceStr);
                musthaveList.set(index,replaceStr);
            }else {
                //当前大括号不存在问号
                replaceStr = StringUtils.strip(replaceStr, "[]");
                sql = sql.replace(musthaveList.get(index), replaceStr);
                index++;
            }
        } else {
            sql = sql.replaceFirst("[?]", replaceValue);
        }
        resultVO.setSql(sql);
        resultVO.setIndex(index);
        resultVO.setMusthaveList(musthaveList);
        return resultVO;
    }

    public static List<String> getList(String managers) {
        List<String> ls = new ArrayList<>();
        String regex = "\\[(.*?)]";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(managers);
        while (matcher.find()) {
            ls.add(matcher.group());
        }
        return ls;
    }

    public static void main(String[] args) {
        String s = "select * from product where 1=1  and name like '%自定义%'";
        String regex = ".*(from.*)$";
        String result = s.replaceAll(regex, "SELECT count(*) $1");
        System.out.println(result);
    }
}
